﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using System.ComponentModel;

namespace www.serviciipeweb.ro.CDC
{
    //TODO:sp_cdc_get_cleanup_retention found retentions
    public class TableCDC
    {
        [Category("DefaultProps"), Description("Original Table")]
        public Table parent { get; private set; }
        public TableCDC(Table t)
        {
            parent = t;
            InitializeSource();
        }
        [Category("DefaultProps"), Description("Table Name")]
        public string Name
        {
            get
            {
                return parent.Name;
            }
            
        }
        [Category("DefaultProps"), Description("Enable or disable Change Data Capture")]
        public bool CDCEnabled
        {
            get
            {
                return parent.IsCDCEnabled();
            }
            set
            {
                Database d = parent.Parent;
                Server s = d.Parent;
                string sql = "exec " + d.Name;
                if (value)
                {

                    if (!parent.HasPrimaryKeyIdentity())
                    {
                        //TODO : error
                        return;
                    }
                    //for Sql server CTP
                    //sql = sql + @".sys.sp_cdc_enable_table_change_data_capture 
                    sql = sql + @".sys.sp_cdc_enable_table 
                    @source_schema = N'dbo'
                  , @source_name = N'" + Name + @"'
                  , @role_name = null
                  , @capture_instance =	 N'" + Name + @"' 
                  , @supports_net_changes = 1";



                }
                else
                {
                    //For ctp
                    //sql = sql + @".sys.sp_cdc_disable_table_change_data_capture 
                    sql = sql + @".sys.sp_cdc_disable_table 
                    @source_schema = N'dbo'
                  , @source_name = N'" + Name + @"'                   
                  , @capture_instance =	 N'" + Name + "'";


                }

                SQLHelperLSN.ExecuteNonQuery(sql);


                Refresh();
            }
        }

        private void InitializeSource()
        {
            if (!CDCEnabled)
                return;

            //TODO : find only for selected table from the begining
            //TODO next version: find multiple schema
            //TODO : raise an question for MS why can't we pass only source name
            Database d = parent.Parent;
            Server s = d.Parent;
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = "exec " + d.Name + ".sys.sp_cdc_help_change_data_capture ";
                    sc.Connection = scon;
                    using (SqlDataReader sr = sc.ExecuteReader())
                    {
                        while (sr.Read())
                        {
                            //console.writeline("inainte de read");
                            if (sr["source_table"].ToString() == Name)
                            {
                                //console.writeline("found ");
                                SourceSchema = SqlUtilities<string>.GetValueOrDefault(sr, "source_schema");

                                Supports_net_changes = SqlUtilities<bool>.GetValueOrDefault(sr, "supports_net_changes");
                                index_name = SqlUtilities<string>.GetValueOrDefault(sr, "index_name");
                                create_date = SqlUtilities<DateTime>.GetValueOrDefault(sr, "create_date");
                                index_column_list = SqlUtilities<string>.GetValueOrDefault(sr, "index_column_list");
                                captured_column_list = SqlUtilities<string>.GetValueOrDefault(sr, "captured_column_list");
                                //Start_LSN = SqlUtilities<byte[]>.GetValueOrDefault(sr, "start_lsn");
                                object o = sr["start_lsn"];
                                if (o != null && o != DBNull.Value)
                                {

                                    Start_LSN = o as byte[];
                                }
                                //console.writeline("found startxy" + (Start_LSN != null) + (sr["start_lsn"] != null) + (sr["start_lsn"] != DBNull.Value));
                                //return;
                            }
                        }
                    }
                }
            }
        }

        #region cdc properties
        [Category("cdc properties"), Description("Source Schema")]
        public string SourceSchema { get;private  set; }
        [Category("cdc properties"), Description("first capture LSN")]
        public byte[] Start_LSN { get; internal set; }
        [Category("cdc properties"), Description("Supports changes retrieving after PK")]
        public bool? Supports_net_changes { get; internal set; }
        [Category("cdc properties"), Description("PK name")]
        public string index_name { get; internal set; }
        [Category("cdc properties"), Description("Date when CDC was enabled")]
        public DateTime? create_date { get; internal set; }
        [Category("cdc properties"), Description("columns participating in PK")]
        public string index_column_list { get; internal set; }
        [Category("cdc properties"), Description("Captured columns")]
        public string captured_column_list { get; internal set; }
        #endregion
        public void Refresh() 
        {
            //console.writeline("in refresh");
            SourceSchema = null;
            Start_LSN = null;
            Supports_net_changes = null;
            index_name= null;
            create_date = null;
            index_column_list = null;
            captured_column_list = null;
            InitializeSource();  
        }
        [Category("cdc properties"), Description("first capture Date time")]
        public DateTime? FirstCapture
        {
            get
            {
                //console.writeline("first capture begin" + (Start_LSN != null));
                if (Start_LSN == null)
                    return null;
                ////console.writeline("first capture :" + Start_LSN);
                Server s = parent.Parent.Parent;

                return SQLHelperLSN.LSN2Time(Start_LSN,parent.Parent.Name);

                //return SqlUtilities<DateTime>.GetValueOrDefault(o);

            }
        }

        public TableChangesCollection AllChanges()
        {
            
            
                if (!CDCEnabled)
                    return null;

                if (Start_LSN == null)
                    Refresh();

                //second verify
                if (Start_LSN == null)
                    return null;

                //TODO : verify firstcapture
                
                TableChangesCollection tc = new TableChangesCollection(this);
                tc.Initialize();
                return tc;
            
        }
        /*
         USE Andrei;
GO
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
-- Obtain the beginning of the time interval.
SET @begin_time = GETDATE()-1;
-- Obtain the end of the time interval.
SET @end_time = GETDATE()+1;
-- Map the time interval to a change data capture query range.
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

-- Return the changes occurring within the query window.
SELECT * FROM cdc.[fn_cdc_get_all_changes_Andrei] (@from_lsn, @to_lsn, 'all');

         */

    }
}
